import sqlite3
import pandas as pd
import os
import time

def create_media_user_wide_table():
    # 连接到SQLite数据库
    db_path = os.path.join('c:\\Users\\Administrator\\RuoYi-Vue01\\sql', 'media_labels.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 创建广电用户宽表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS media_user_wide_table (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        terminal_no TEXT,
        phone_no TEXT,
        sm_name TEXT,
        run_name TEXT,
        sm_code TEXT,
        owner_name TEXT,
        owner_code TEXT,
        run_time TEXT,
        addressoj TEXT,
        estate_name TEXT,
        open_time TEXT,
        force TEXT,
        user_label TEXT,
        label_type TEXT,
        label_new_user INTEGER,
        label_potential_churn INTEGER,
        label_regular_user INTEGER,
        label_loyal_user INTEGER,
        label_high_value INTEGER,
        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    ''')
    
    # 创建索引
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_media_user_terminal_no ON media_user_wide_table(terminal_no)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_media_user_phone_no ON media_user_wide_table(phone_no)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_media_user_label ON media_user_wide_table(user_label)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_media_user_label_type ON media_user_wide_table(label_type)')
    
    # 读取CSV文件
    csv_path = 'c:\\Users\\Administrator\\RuoYi-Vue01\\python_project\\media_users_wide_table.csv'
    print(f"正在读取CSV文件: {csv_path}")
    
    # 使用pandas读取CSV，指定分隔符为分号
    df = pd.read_csv(csv_path, sep=';', encoding='utf-8')
    
    # 显示数据基本信息
    print(f"CSV文件共包含 {len(df)} 条记录")
    print(f"列名: {list(df.columns)}")
    
    # 清空表数据（避免重复插入）
    cursor.execute('DELETE FROM media_user_wide_table')
    
    # 准备插入数据
    insert_count = 0
    start_time = time.time()
    
    # 分批插入数据
    batch_size = 1000
    for i in range(0, len(df), batch_size):
        batch = df.iloc[i:i+batch_size]
        values_list = []
        
        for _, row in batch.iterrows():
            # 处理列名映射
            values = (
                row.get('terminal_no', ''),
                row.get('phone_no', ''),
                row.get('sm_name', ''),
                row.get('run_name', ''),
                row.get('sm_code', ''),
                row.get('owner_name', ''),
                row.get('owner_code', ''),
                row.get('run_time', ''),
                row.get('addressoj', ''),
                row.get('estate_name', ''),
                row.get('open_time', ''),
                row.get('force', ''),
                row.get('user_label', ''),
                row.get('label_type', ''),
                row.get('label_新用户', 0),
                row.get('label_潜在流失用户', 0),
                row.get('label_一般用户', 0),
                row.get('label_忠诚用户', 0),
                row.get('label_高价值用户', 0)
            )
            values_list.append(values)
        
        # 批量插入
        cursor.executemany('''
        INSERT INTO media_user_wide_table 
        (terminal_no, phone_no, sm_name, run_name, sm_code, owner_name, owner_code, run_time, 
         addressoj, estate_name, open_time, force, user_label, label_type, 
         label_new_user, label_potential_churn, label_regular_user, label_loyal_user, label_high_value)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', values_list)
        
        insert_count += len(values_list)
        print(f"已插入 {insert_count}/{len(df)} 条记录...")
    
    # 提交事务
    conn.commit()
    
    # 验证数据
    cursor.execute('SELECT COUNT(*) FROM media_user_wide_table')
    total_count = cursor.fetchone()[0]
    
    cursor.execute('SELECT * FROM media_user_wide_table LIMIT 5')
    sample_data = cursor.fetchall()
    
    # 统计标签分布
    print("\n=== 标签分布统计 ===")
    cursor.execute('SELECT user_label, COUNT(*) as count FROM media_user_wide_table GROUP BY user_label ORDER BY count DESC')
    label_stats = cursor.fetchall()
    for label, count in label_stats:
        print(f"{label}: {count}条")
    
    # 关闭连接
    conn.close()
    
    end_time = time.time()
    print(f"\n宽表创建成功！")
    print(f"总记录数: {total_count}")
    print(f"耗时: {end_time - start_time:.2f}秒")
    print(f"数据库保存在: {db_path}")
    
    return total_count

if __name__ == "__main__":
    create_media_user_wide_table()